﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class DBHerpel
{
    static string strCon = "server=.;database=AAA;uid=sa;pwd=admin";

    //增删改
    public static int MyExecuteNonQuery(string sql)
    {
        using (SqlConnection objCon = new SqlConnection(strCon))
        {
            objCon.Open();
            SqlCommand objCmd = new SqlCommand(sql, objCon);
            return objCmd.ExecuteNonQuery(); ;
        }
    }

    //带参数Sql命令的增删改
    public static int MyExecuteNonQuery(string sql, SqlParameter[] paras)
    {
        using (SqlConnection objCon = new SqlConnection(strCon))
        {
            objCon.Open();
            SqlCommand objCmd = new SqlCommand(sql, objCon);
            objCmd.Parameters.AddRange(paras);
            return objCmd.ExecuteNonQuery();
        }
    }

    //单值查询
    public static object MyExecuteScalar(string sql)
    {
        using (SqlConnection objCon = new SqlConnection(strCon))
        {
            objCon.Open();
            SqlCommand objCmd = new SqlCommand(sql, objCon);
            return objCmd.ExecuteScalar();
        }
    }

    //带参数Sql命令的单值查询
    public static object MyExecuteScalar(string sql, SqlParameter[] paras)
    {
        using (SqlConnection objCon = new SqlConnection(strCon))
        {
            objCon.Open();
            SqlCommand objCom = new SqlCommand(sql, objCon);
            objCom.Parameters.AddRange(paras);
            return objCom.ExecuteScalar();
        }
    }

    //返回DataTable
    public static DataTable QueryData(string sql)
    {
        SqlConnection objCon = new SqlConnection(strCon);
        SqlDataAdapter objDap = new SqlDataAdapter(sql, objCon);
        DataTable dt = new DataTable();
        objDap.Fill(dt);
        return dt;
    }

    //带参数Sql命令返回DataTable
    public static DataTable QueryData(string sql, SqlParameter[] paras)
    {
        SqlConnection objCon = new SqlConnection(strCon);
        SqlDataAdapter objDap = new SqlDataAdapter(sql, objCon);
        objDap.SelectCommand.Parameters.AddRange(paras);
        DataTable dt = new DataTable();
        objDap.Fill(dt);
        return dt;
    }

    //返回DataReader
    public static SqlDataReader MyDataReader(string sql)
    {
        SqlConnection objCon = null;
        try
        {
            objCon = new SqlConnection(strCon);
            objCon.Open();
            SqlCommand com = new SqlCommand(sql, objCon);
            return com.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            if (objCon.State == ConnectionState.Open)
            {
                objCon.Close();
            }
            throw ex;
        }
    }

    //带参数Sql命令返回DataReader
    public static SqlDataReader MyDataReader(string sql, SqlParameter[] paras)
    {
        SqlConnection objCon = null;
        try
        {
            objCon = new SqlConnection(strCon);
            objCon.Open();
            SqlCommand objCmd = new SqlCommand(sql, objCon);
            if (paras != null)
            {
                objCmd.Parameters.AddRange(paras);

            }

            return objCmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            if (objCon.State == ConnectionState.Open)
            {
                objCon.Close();
            }
            throw ex;
        }
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="procName"></param>
    /// <param name="paras"></param>
    /// <returns></returns>
    public static int MyProcedure(string procName, SqlParameter[] paras)
    {
        using (SqlConnection objCon = new SqlConnection(strCon))
        {
            objCon.Open();
            SqlCommand objCom = new SqlCommand(procName, objCon);
            objCom.CommandType = CommandType.StoredProcedure;
            objCom.Parameters.AddRange(paras);
            return objCom.ExecuteNonQuery();
        }
    }
    
}
